L24: Valuation sensitivity analysis

The need for sensitivity analysis

Our valuation results suggest that there is quite a bit of variance in our estimates of the value of MSFT. We have to remember that, even though we obtained many different estimates for this value, each of those estimates relies on some pretty strict assumptions that could easily not hold in the future.

The purpose of a sensitivity analysis is to obtain an idea of just how much our estimates vary when we change some of the crucial assumptions behind our valuation methods.

The showcase a sensitivity analysis, we use one of the valuation methods above: the DCF based on FCFE approach, and we rerun the valuation changing two crucial parameters we used for it:

  • The perpetual growth rate (g) and
  • The cost of capital (r_e)

These parameters: the perpetual growth rate and the discount rate, have the highest impact on any DCF and DDM valuation.

To perform the sensitivity analysis in Excel:

  • We create a table where
    • the columns are the different cost of capital values we want to use
    • the rows are the different growth rates we want to use
  • In the top left corner of the table, we link to the valuation cell from the original analysis
  • Select the entire table
  • Go to Data -> What-if analysis -> Data Table
    • for “Row Cell” select the cost of equity cell in the original analysis
    • for “Column Cell” select the terminal growth rate cell in the original analysis
#
#
#
#
#
#
#
#
#
#